﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;


namespace olierFrm.DataBase
{
    internal class ParseSQL
    {

        public const string EndRegex = "--ENDPAGING";
        public const string StartRegex = "--STARTPAGING";


        internal ParseSQL(string strSQL, int pageIndex, int pageRowCount, out string outSQL)
        {
            //strSQL = strSQL.ToUpper();

            //string before = strSQL.Substring(0, strSQL.IndexOf("--STARTPAGING", StringComparison.OrdinalIgnoreCase));

            //string content = strSQL.Substring(strSQL.IndexOf("--STARTPAGING", StringComparison.OrdinalIgnoreCase), 13);
            //content = content.Substring(0, content.IndexOf("--ENDPAGING", StringComparison.OrdinalIgnoreCase) + 11);

            //string after = strSQL.Substring(strSQL.IndexOf("--ENDPAGING", StringComparison.OrdinalIgnoreCase) + 11);


            int startIndex = strSQL.IndexOf("--STARTPAGING", StringComparison.OrdinalIgnoreCase);
            string startBefore = (startIndex != -1) ? strSQL.Substring(0, startIndex) : "";

            string newsql = strSQL.Substring((startIndex == -1) ? 0 : startIndex);

            int endIndex = newsql.IndexOf("--ENDPAGING", StringComparison.OrdinalIgnoreCase);
            string endAfter = (endIndex != -1) ? newsql.Substring(endIndex) : "";

            newsql = newsql.Substring(0, (endIndex == -1) ? strSQL.Length : endIndex);

            newsql = new Regex("(--STARTPAGING)|(--ENDPAGING)", RegexOptions.IgnoreCase).Replace(newsql, "");
            string strFromAfter = "";
            try
            {
                strFromAfter = newsql.Substring(newsql.LastIndexOf("FROM", StringComparison.OrdinalIgnoreCase));
            }
            catch
            {
                throw new Exception("这不是有效的SQL语句。");
            }
            string orderySql = this.ParseOrderCols(strFromAfter);
            orderySql = (orderySql == "") ? "ORDER BY (SELECT 0)" : orderySql;
            if (this.ParseGroupCols(strFromAfter) != "")
            {
                outSQL = string.Concat(new object[] { 
                    startBefore,
                    "SELECT * FROM (SELECT ROW_NUMBER() OVER (", orderySql, ") AS [ROW_NUMBER],* FROM (",
                    new Regex(@"(ORDER\s+BY)([\s\S]*)($)").Replace(newsql, ""), 
                    ") AS [tempPaging_1]) AS [tempPaging_2] WHERE [tempPaging_2].[ROW_NUMBER] BETWEEN ", 
                    pageIndex, " AND ", pageRowCount, " ",
                    endAfter });
            }
            else
            {

                //var sss = newsql.Substring(newsql.IndexOf("SELECT") + 6, newsql.LastIndexOf("order by", StringComparison.OrdinalIgnoreCase) - 10);
                var regex = new Regex(@"ORDER\s+BY", RegexOptions.IgnoreCase);
                //var regex = new Regex(@"(ORDER\s+BY)([\s\S]*)($)", RegexOptions.ECMAScript);
                var maths = regex.Matches(newsql);
                var math = maths[maths.Count - 1];
                string sqlContent = newsql.Substring(0, math.Index);
                sqlContent = sqlContent.Substring(sqlContent.IndexOf("SELECT", StringComparison.OrdinalIgnoreCase) + 6);

                string replaceOrderBy = new Regex(@"(ORDER\s+BY)([\s\S]*)($)\1", RegexOptions.RightToLeft).Replace(newsql, "").Substring(newsql.IndexOf("SELECT", StringComparison.OrdinalIgnoreCase) + 6);
                outSQL = string.Concat(new object[] {
                    startBefore,
                    "SELECT * FROM (SELECT ROW_NUMBER() OVER(", orderySql, ") AS [ROW_NUMBER],", 
                    sqlContent,
                    ") AS [tempPaging_1] WHERE [tempPaging_1].[ROW_NUMBER] BETWEEN ",
                    pageIndex , " AND ", 
                    pageRowCount, " ",
                    endAfter });
            }

        }

        private bool isContains(string sql, string regex)
        {
            Regex r = new Regex(regex);
            return r.IsMatch(sql);
        }

        private string MatchString(string sql, string regex)
        {
            var reg = new Regex(regex, RegexOptions.IgnoreCase);
            var match = reg.Match(sql);
            if (match.Length != 0)
            {
                return match.Value;
            }
            return "";
        }

        private string ParseCols(string sql)
        {
            return this.MatchString(sql, @"(SELECT)([\s\S]*)(FROM)");
        }

        private string ParseConditions(string sql)
        {
            string regex = "";
            if (!this.isContains(sql, @"\s+WHERE\s+"))
            {
                return "";
            }
            if (this.isContains(sql, @"GROUP\s+BY"))
            {
                regex = @"(WHERE)([\s\S]*)(GROUP\s+BY)";
            }
            else if (this.isContains(sql, @"order\s+by"))
            {
                regex = @"(WHERE)([\s\S]*)(ORDER\s+BY)";
            }
            else
            {
                regex = @"(WHERE)([\s\S]*)($)";
            }
            return new Regex(@"(ORDER\s+BY)").Replace(new Regex(@"(GROUP\s+BY)").Replace(this.MatchString(sql, regex), ""), "");
        }

        private string ParseGroupCols(string sql)
        {
            string regex = "";
            if (!this.isContains(sql, @"GROUP\s+BY"))
            {
                return "";
            }
            if (this.isContains(sql, @"ORDER\s+BY"))
            {
                regex = @"(GROUP\s+BY)([\s\S]*)(ORDER\s+BY)";
            }
            else
            {
                regex = @"(GROUP\s+BY)([\s\S]*)($)";
            }
            return new Regex(@"(ORDER\s+BY)").Replace(this.MatchString(sql, regex), "");
        }

        private string ParseOrderCols(string sql)
        {
            string regex = @"(ORDER\s+BY)([\s\S]*)($)";
            return this.MatchString(sql, regex);
        }
    }





}
